package com.atzon.DAO;

import com.atzon.constant.Const;
import com.atzon.model.Dept;
import com.atzon.utils.JDBCUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;

import java.sql.SQLException;
import java.sql.Time;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class MeetDAO extends BaseDAO {
    public void addJoin(Integer uId, Integer cId) {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        try {
            int update = qr.update("update con_join set status = ? where u_id = ? and c_id = ? ", 1, uId, cId);
            if (update == 0) {
                qr.update("insert into con_join value(?,?,?,?)", null, uId, cId, 1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void delJoin(Integer uId, Integer cId) {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        try {
            qr.update("update con_join set status = ? where u_id = ? and c_id = ? ", 0, uId, cId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void turnstu(Integer status, Integer cId) {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        try {
            qr.update("update conference set status = ? where id = ? ", status, cId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void addMeet(String dept, String title, String content, String start, Integer uId) {
        try {
            QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
            Date now = new Date();
            SimpleDateFormat format = new SimpleDateFormat("HH:mm");
            Time time = new Time(format.parse(start).getTime());
            Dept newDept = findDeptByName(dept);
            qr.update("insert into conference value(?,?,?,?,?,?,?,?,?)",
                    null, dept, newDept.getId(), title, content, now, time, 1, uId);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public long getTotalMeet(String search, String dept) {
        long total = 0L;
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        try {
            StringBuilder sql = new StringBuilder();
            sql.append("select count(*) as total from conference ");
            if (null == search && null == dept) {
                Map<String, Object> map = qr.query(sql.toString(), new MapHandler());
                total = Long.parseLong(map.get("total").toString());
            } else {
                if (null != search && null == dept) {
                    sql.append("where title like ?");
                    search = search + "%";
                    Map<String, Object> map = qr.query(sql.toString(), new MapHandler(), search);
                    total = Long.parseLong(map.get("total").toString());
                } else if (null == search && null != dept) {
                    sql.append("where dept_name = ? ");
                    Map<String, Object> map = qr.query(sql.toString(), new MapHandler(), dept);
                    total = Long.parseLong(map.get("total").toString());
                } else {
                    sql.append("where title like ? and dept_name = ? ");
                    search = search + "%";
                    Map<String, Object> map = qr.query(sql.toString(), new MapHandler(), search, dept);
                    total = Long.parseLong(map.get("total").toString());
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return total;
    }

    public Map<String, Object> findMeetById(Integer meetId) {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        Map<String, Object> map = null;
        try {
            StringBuilder sql = new StringBuilder();
            sql.append("select conf.id, conf.dept_name as deptName, conf.dept_id as deptId, conf.title, ");
            sql.append("conf.content, conf.publish_date as pubDate, conf.start_time as begTime, conf.status,conf.user_id as confUId, c.status as come, ");
            sql.append("(select count(*) from user u where conf.dept_id = u.dept_id) as utotal, ");
            sql.append("(select count(*) from con_join c where c_id = ? and status = ?) as jtotal ");
            sql.append("from conference conf ");
            sql.append("left join con_join c on conf.id = c.c_id ");
            sql.append("where conf.id = ? ");
            map = qr.query(sql.toString(), new MapHandler(), meetId, 1, meetId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return map;
    }

    public List<Map<String, Object>> findAllDept() {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        List<Map<String, Object>> list = new ArrayList<>();
        try {
            StringBuilder sql = new StringBuilder();
            sql.append("select d.id, d.name, ");
            sql.append("(select count(*) as total from user u where d.id = u.dept_id) as follows ");
            sql.append("from dept d ");
            list = qr.query(sql.toString(), new MapListHandler());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public List<Map<String, Object>> findUser() {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        List<Map<String, Object>> list = new ArrayList<>();
        try {
            StringBuilder sql = new StringBuilder();
            sql.append("select u.id, u.username, u.dept_id as deptId, ui.real_name as realName ");
            sql.append("from user u ");
            sql.append("left join user_info ui on u.id = ui.user_id ");
            list = qr.query(sql.toString(), new MapListHandler());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public List<Map<String, Object>> findMeet(int page, String search, String dept) {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        List<Map<String, Object>> maps = new ArrayList<>();
        try {
            StringBuilder sql = new StringBuilder();
            sql.append("select conf.id, conf.dept_name as deptName, conf.dept_id as deptId, conf.title, ");
            sql.append("conf.content, conf.publish_date as pubDate, conf.start_time as begTime, conf.status ");
            sql.append("from conference conf ");
            if (null == search && null == dept) {
                sql.append("order by id limit ?,? ");
                maps = qr.query(sql.toString(), new MapListHandler(), (page - 1) * Const.MAX_PAGE_SIZE, Const.MAX_PAGE_SIZE);
            } else {
                if (null != search && null == dept) {
                    sql.append("where conf.title like ?");
                    sql.append("order by id limit ?,? ");
                    search = search + "%";
                    maps = qr.query(sql.toString(), new MapListHandler(), search, (page - 1) * Const.MAX_PAGE_SIZE, Const.MAX_PAGE_SIZE);
                } else if (null == search && null != dept) {
                    sql.append("where conf.dept_name = ? ");
                    sql.append("order by id limit ?,? ");
                    maps = qr.query(sql.toString(), new MapListHandler(), dept, (page - 1) * Const.MAX_PAGE_SIZE, Const.MAX_PAGE_SIZE);
                } else {
                    sql.append("where conf.title like ? and conf.dept_name = ? ");
                    sql.append("order by id limit ?,? ");
                    search = search + "%";
                    maps = qr.query(sql.toString(), new MapListHandler(), search, dept, (page - 1) * Const.MAX_PAGE_SIZE, Const.MAX_PAGE_SIZE);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return maps;
    }
}
